const mysql = require('mysql2/promise');

const dbConfig = {
    host: '43.143.253.188',
    port: 3306,
    user: 'nextjs',
    password: 'yEAiRE2jcxTyRBy5',
    database: 'nextjs'
};

async function createPrivateMessagesTable() {
    let connection;

    try {
        console.log('🔌 连接到数据库...');
        connection = await mysql.createConnection(dbConfig);
        console.log('✅ 数据库连接成功');

        // 创建私信消息表
        console.log('📋 创建private_messages表...');
        const createTableSQL = `
            CREATE TABLE IF NOT EXISTS \`private_messages\` (
                \`id\` bigint NOT NULL AUTO_INCREMENT COMMENT '私信消息ID',
                \`content\` text NOT NULL COMMENT '消息内容',
                \`senderName\` varchar(64) NOT NULL COMMENT '发送者名称',
                \`senderCharacterId\` bigint NOT NULL COMMENT '发送者角色ID',
                \`receiverName\` varchar(64) NOT NULL COMMENT '接收者名称',
                \`receiverCharacterId\` bigint NOT NULL COMMENT '接收者角色ID',
                \`messageTime\` varchar(50) NOT NULL COMMENT '消息时间显示格式 (如: 2025-09-02 08:29:08)',
                \`isActive\` tinyint NOT NULL DEFAULT 1 COMMENT '是否有效 (1:有效, 0:已删除)',
                \`isRead\` tinyint NOT NULL DEFAULT 0 COMMENT '是否已读 (0:未读, 1:已读)',
                \`status\` tinyint NOT NULL DEFAULT 0 COMMENT '消息状态 (0:正常, 1:已撤回, 2:被删除)',
                \`messageType\` tinyint NOT NULL DEFAULT 1 COMMENT '消息类型 (1:玩家消息, 2:系统消息)',
                \`createdAt\` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                \`updatedAt\` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                PRIMARY KEY (\`id\`),
                KEY \`idx_sender_receiver\` (\`senderCharacterId\`, \`receiverCharacterId\`),
                KEY \`idx_receiver_created\` (\`receiverCharacterId\`, \`createdAt\`),
                KEY \`idx_sender_created\` (\`senderCharacterId\`, \`createdAt\`),
                KEY \`idx_is_active\` (\`isActive\`),
                KEY \`idx_is_read\` (\`isRead\`),
                KEY \`idx_status\` (\`status\`),
                KEY \`idx_created_at\` (\`createdAt\`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='私信消息表'
        `;

        await connection.execute(createTableSQL);
        console.log('✅ private_messages表创建成功');

        // 插入一些测试数据
        console.log('📝 插入测试数据...');
        const insertTestDataSQL = `
            INSERT INTO \`private_messages\` (
                \`content\`, \`senderName\`, \`senderCharacterId\`, \`receiverName\`, \`receiverCharacterId\`, 
                \`messageTime\`, \`isActive\`, \`isRead\`, \`status\`, \`messageType\`, \`createdAt\`, \`updatedAt\`
            ) VALUES 
            (
                '你好，这是私聊消息', '三国新人', 1001, '测试角色', 1002,
                '2025-09-02 08:29:08', 1, 0, 0, 1, NOW(), NOW()
            ),
            (
                '收到，你好！', '测试角色', 1002, '三国新人', 1001,
                '2025-09-02 08:30:15', 1, 0, 0, 1, NOW(), NOW()
            )
        `;

        try {
            await connection.execute(insertTestDataSQL);
            console.log('✅ 测试数据插入成功');
        } catch (error) {
            if (error.code === 'ER_DUP_ENTRY') {
                console.log('ℹ️ 测试数据已存在，跳过插入');
            } else {
                console.log('⚠️ 测试数据插入失败（可能是数据已存在）:', error.message);
            }
        }

        // 验证表结构
        console.log('🔍 验证表结构...');
        const [rows] = await connection.execute('DESCRIBE `private_messages`');
        console.log('📊 表结构:');
        rows.forEach(row => {
            console.log(`  ${row.Field}: ${row.Type} ${row.Null === 'NO' ? 'NOT NULL' : 'NULL'} ${row.Default ? `DEFAULT ${row.Default}` : ''} ${row.Comment ? `COMMENT '${row.Comment}'` : ''}`);
        });

        // 验证索引
        console.log('🔍 验证索引...');
        const [indexes] = await connection.execute('SHOW INDEX FROM `private_messages`');
        console.log('📊 索引信息:');
        indexes.forEach(index => {
            console.log(`  ${index.Key_name}: ${index.Column_name} (${index.Cardinality})`);
        });

        console.log('🎉 私信消息表创建完成！');

    } catch (error) {
        console.error('❌ 创建私信消息表失败:', error);
        throw error;
    } finally {
        if (connection) {
            await connection.end();
            console.log('🔌 数据库连接已关闭');
        }
    }
}

// 如果直接运行此脚本
if (require.main === module) {
    createPrivateMessagesTable()
        .then(() => {
            console.log('✅ 脚本执行完成');
            process.exit(0);
        })
        .catch((error) => {
            console.error('❌ 脚本执行失败:', error);
            process.exit(1);
        });
}

module.exports = { createPrivateMessagesTable };
